package com.aisino.inveli.vinda.utils;

import com.aisino.inveli.vinda.entity.ConfigEntity;
import com.aisino.inveli.vinda.entity.GoodsDetail;
import com.aisino.inveli.vinda.entity.GoodsList;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * @author leifeng
 * @Date 2017-11-16
 * excel 文件操作类
 **/
public class ExcelUtils {

    private static String[] xlsHeader = { "单据号", "购方名称", "购方税号", "购方地址电话", "银行账号",
            "复核人", "收款人", "商品名称", "单位", "规格", "数量", "金额", "税率", "折扣金额", "税额",
            "折扣税额", "税收分类编码", "税收分类编码版本号" };
    private static String[] xlsHeaderByNoRate = { "单据号", "购方名称", "购方税号", "购方地址电话",
            "银行账号", "复核人", "收款人", "商品名称", "单位", "规格", "数量", "金额（不含税）", "税率",
            "折扣金额（不含税）", "税额", "折扣税额", "税收分类编码", "税收分类编码版本号" };

    /**
     * 生成含税excel
     * @param sysGoodList 商品
     * @param sysConfigEntity 信息配置
     */
    public static JSONObject getXLSFile(GoodsList sysGoodList, ConfigEntity sysConfigEntity) {
        JSONObject json = new JSONObject();
        String sysPATH = sysConfigEntity.getOutputPath();
        File sysFile = new File(sysPATH);
        if (!sysFile.exists()) {
            sysFile.mkdirs();
        }
        String[] header = getHeaderArray(sysGoodList.getHeaderStr());
        List<String[]> strList = getZSListArray(sysGoodList.getResultZS(),sysConfigEntity);
        String excelFileName = sysPATH + "/change_"
                + header[0]+".xls";
        String sheetName = "Sheet1";
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        //第一行遍历头部信息，写成标题
        HSSFRow tempr = sheet.createRow(0);
        for (int c = 0; c < xlsHeader.length; c++) {
            HSSFCell cell = tempr.createCell(c);
            cell.setCellValue(xlsHeader[c]);
        }
        //遍历商品信息从第二行开始
        for (int r = 1; r <= strList.size(); r++) {
            HSSFRow row = sheet.createRow(r);
            // 写入购方信息
            for (int c = 0; c < header.length; c++) {
                HSSFCell cell = row.createCell(c);
                cell.setCellValue(header[c]);
            }
            // 收款人+复核人+商品信息
            for (int c = header.length; c < header.length
                    + strList.get(0).length; c++) {
                HSSFCell cell = row.createCell(c);
                cell.setCellValue(strList.get(r - 1)[c - header.length]);
            }

        }
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(excelFileName);
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            json.put("status",true);
            return json;
        } catch (Exception e) {
            e.printStackTrace();
            json.put("status",false);
            json.put("message",e.getMessage());
            return json;
        }
    }

    /**
     * 获取商品信息
     * @param resultZS
     * @param sysConfigEntity
     * @return
     */
    private static List<String[]> getZSListArray(List<GoodsDetail> resultZS, ConfigEntity sysConfigEntity) {
        List<String[]> list = new ArrayList<>();
        // BigDecimal dis = new BigDecimal(getDiscount());
        BigDecimal tm = new BigDecimal(0);
        BigDecimal to = new BigDecimal(0);
        for (int i = 0; i < resultZS.size(); i++) {
            //当前对象
            GoodsDetail fsg = resultZS.get(i);
            if (fsg.isO()) {
                continue;
            }
            //含税金额
            BigDecimal mon = new BigDecimal(fsg.getNewMon() == null ? fsg
                    .getMon() : fsg.getNewMon());
            //税率
            BigDecimal rate = new BigDecimal(sysConfigEntity.getRate());
            String num;//实际数量
            if (fsg.getNewNum() != null && fsg.getNewNum().trim().length() != 0) {
                num = fsg.getNewNum();
            } else {
                if (fsg.getNum() != null && fsg.getNum().trim().length() != 0) {
                    num = fsg.getNum();
                } else {
                    num = "";
                }
            }
            //折扣金额
            BigDecimal disMon = new BigDecimal(fsg.getDisMon());
            BigDecimal rateMon;//税额
            if (fsg.getRateMon() == null
                    || fsg.getRateMon().trim().length() == 0) {
                rateMon = mon.multiply(rate);
                BigDecimal t = new BigDecimal(1);
                t = t.add(rate);
                rateMon = rateMon.divide(t, 2, BigDecimal.ROUND_HALF_UP);
            } else {
                rateMon = new BigDecimal(fsg.getRateMon());
            }
            String[] sa = new String[13];
            sa[0] = sysConfigEntity.getChecker();
            sa[1] = sysConfigEntity.getPayee();
            sa[2] = fsg.getName();
            sa[3] = fsg.getDw();
            sa[4] = fsg.getType();
            sa[5] = num;
            sa[6] = mon.toString();
            sa[7] = fsg.getRate();
            sa[8] = MathUtil.getTwoPointBig(disMon).toString();
            sa[9] = rateMon + "";
            BigDecimal temp = MathUtil.getTwoPointBig(
                    disMon.multiply(new BigDecimal(fsg.getRate()))
                            .divide(new BigDecimal(1).add(new BigDecimal(fsg.getRate()))
                                    , 2, BigDecimal.ROUND_HALF_UP));
            to= to.add(temp);
            sa[10] = temp.toString();
            sa[11] = fsg.getSsflbm();
            sa[12] = "13.0";
            list.add(sa);
            tm = tm.add(mon);
        }
        return list;
    }

    /**
     * 获取头部购方信息
     * @param headerStr
     * @return
     */
    public static String[] getHeaderArray(String headerStr) {
        String[] sa = headerStr.split("~~");
        String[] result = new String[5];
        //单据号
        result[0] = sa[0];
        //购方名称
        result[1] = sa[3];
        //购方税号
        result[2] = sa[4];
        //购方地址电话
        result[3] = sa[5];
        //银行账号
        result[4] = sa[6];
        // result[5] = sa[sa.length-1];
        return result;
    }

    /**
     * 生成不含税Excel
     * @param sysGoodList
     * @param sysConfigEntity
     * @return
     */
    public static JSONObject getXLSFileByNoRateMon(GoodsList sysGoodList, ConfigEntity sysConfigEntity) {
        JSONObject json = new JSONObject();
        String sysPATH = sysConfigEntity.getOutputPath();
        sysGoodList = getNoRateFSGL(sysGoodList);
        File sysFile = new File(sysPATH);
        if (!sysFile.exists()) {
            sysFile.mkdirs();
        }
        String[] header = getHeaderArray(sysGoodList.getHeaderStr());
        List<String[]> strList = getZSListArrayByNorate(sysGoodList.getResultZS(),sysConfigEntity);
        String excelFileName = sysPATH + "/change_"
                + header[0]+".xls";
        String sheetName = "Sheet1";
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow tempr = sheet.createRow(0);
        for (int c = 0; c < xlsHeaderByNoRate.length; c++) {
            HSSFCell cell = tempr.createCell(c);
            cell.setCellValue(xlsHeaderByNoRate[c]);
        }
        for (int r = 1; r <= strList.size(); r++) {
            HSSFRow row = sheet.createRow(r);
            // 头文件
            for (int c = 0; c < header.length; c++) {
                HSSFCell cell = row.createCell(c);
                cell.setCellValue(header[c]);
            }
            // 收款人+复核人+商品信息
            for (int c = header.length; c < header.length
                    + strList.get(0).length; c++) {
                HSSFCell cell = row.createCell(c);
                cell.setCellValue(strList.get(r - 1)[c - header.length]);
            }

        }
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(excelFileName);
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            json.put("status",true);
            return json;
        } catch (Exception e) {
            e.printStackTrace();
            json.put("status",false);
            json.put("message",e.getMessage());
            return json;
        }
    }

    private static List<String[]> getZSListArrayByNorate(List<GoodsDetail> resultZS, ConfigEntity sysConfigEntity) {
            List<String[]> list = new ArrayList<String[]>();
            // BigDecimal dis = new BigDecimal(getDiscount());
            BigDecimal tm = new BigDecimal(0);
            for (int i = 0; i < resultZS.size(); i++) {
                GoodsDetail fsg = resultZS.get(i);
                if (fsg.isO()) {
                    continue;
                }
                BigDecimal mon = new BigDecimal(fsg.getNewMon() == null ? fsg
                        .getMon() : fsg.getNewMon());
                BigDecimal rate = new BigDecimal(fsg.getRate());
                String num;
                if (fsg.getNewNum() != null && fsg.getNewNum().trim().length() != 0) {
                    num = fsg.getNewNum();
                } else {
                    if (fsg.getNum() != null && fsg.getNum().trim().length() != 0) {
                        num = fsg.getNum();
                    } else {
                        num = "";
                    }
                }
                // BigDecimal disMon = new BigDecimal(fsg.getDisMon());
                BigDecimal rateMon;
                if (fsg.getRateMon() == null
                        || fsg.getRateMon().trim().length() == 0) {
                    rateMon = mon.multiply(rate);
                    BigDecimal t = new BigDecimal(1);
                    t = t.add(rate);
                    rateMon = rateMon.divide(t, 2, BigDecimal.ROUND_HALF_UP);
                } else {
                    rateMon = new BigDecimal(fsg.getRateMon());
                }
                String noRateMonStr = fsg.getNewNoRateMon();
                BigDecimal noRateMon = new BigDecimal(0);
                if (noRateMonStr != null && noRateMonStr.trim().length() != 0) {
                    noRateMon = new BigDecimal(noRateMonStr);
                } else {
                    noRateMon = new BigDecimal(fsg.getNoRateMon());
                }
                BigDecimal noRateDisMon = new BigDecimal(fsg.getNoRateDisMon());
                String[] sa = new String[13];
                sa[0] = sysConfigEntity.getChecker();
                sa[1] = sysConfigEntity.getPayee();
                sa[2] = fsg.getName();
                sa[3] = fsg.getDw();
                sa[4] = fsg.getType();
                sa[5] = num;
                sa[6] = noRateMon.toString();
                sa[7] = fsg.getRate();
                sa[8] = MathUtil.getTwoPointBig(noRateDisMon).toString();
                String rateMonStr = fsg.getRateMon();
                if (rateMonStr != null && rateMonStr.trim().length() != 0) {
                    sa[9] = rateMonStr;
                } else {
                    sa[9] = noRateMon.multiply(new BigDecimal(sysConfigEntity.getRate())).setScale(2,
                            BigDecimal.ROUND_HALF_UP).toString();
                }
                sa[10] = MathUtil.getTwoPointBig(
                        noRateDisMon.multiply(new BigDecimal(sysConfigEntity.getRate()))).toString();
                sa[11] = fsg.getSsflbm();
                sa[12] = "13.0";
                list.add(sa);
                tm = tm.add(mon);
            }
            return list;
    }

    /**
     * 获取不含税商品集合
     * @param fsgl
     * @return
     */
    private static GoodsList getNoRateFSGL(GoodsList fsgl) {
        BigDecimal oracleMon = new BigDecimal(fsgl.getOracleTotalMon());
        BigDecimal theoryOrcleNoRateMon = oracleMon.divide(new BigDecimal("1.17"),2,BigDecimal.ROUND_HALF_UP);
        BigDecimal theoryRate = theoryOrcleNoRateMon.multiply(new BigDecimal("0.17")).setScale(2, BigDecimal.ROUND_HALF_UP);
        BigDecimal tempNoRateMon = new BigDecimal(0);
        BigDecimal tempRateMon = new BigDecimal(0);
        for(GoodsDetail fss: fsgl.getResultZS()){
            String newMonStr = fss.getNewMon();
            BigDecimal mon = new BigDecimal(fss.getMon());
            if(newMonStr!=null&&newMonStr.trim().length()!=0){
                mon = new BigDecimal(newMonStr);
            }
            BigDecimal noRateMon = mon.divide(new BigDecimal("1.17"),2,BigDecimal.ROUND_HALF_UP);

            BigDecimal rateMon = noRateMon.multiply(new BigDecimal("0.17")).setScale(2, BigDecimal.ROUND_HALF_UP);
            fss.setNoRateMon(noRateMon.toString());
            fss.setRateMon(rateMon.toString());
            tempNoRateMon = noRateMon.add(tempNoRateMon);
            tempRateMon = tempRateMon.add(rateMon);
        }
        BigDecimal oracleWC = theoryOrcleNoRateMon.subtract(tempNoRateMon);
        BigDecimal rateMonWc = theoryRate.subtract(tempRateMon);
        //调整误差 orcle不含税
        int realCount = 0;
        for (GoodsDetail fss : fsgl.getResultZS()) {
            if (!fss.isO()) {
                realCount++;
            }
        }
        double wc = oracleWC.doubleValue();
        if(wc!=0) {
            int intwc = 0;
            boolean flag = false;
            if (wc < 0) {
                double f = Math.floor(wc * 100);
                if (f >= wc * 100) {
                    intwc = (int) f;
                } else {
                    intwc = (int) f + 1;
                }
            } else {
                flag = true;
                intwc = (int) Math.floor(wc * 100);
            }
            intwc = Math.abs(intwc);
            List<GoodsDetail> list = fsgl.getResultZS();
            int count = intwc % realCount;
            int countzs = intwc / realCount;

            int index = -1;
            for (int i = 0; i < list.size(); i++) {
                GoodsDetail fsg = list.get(i);
                if (fsg.isO()) {
                    continue;
                }
                BigDecimal tempNRRM =new BigDecimal(fsg.getNoRateMon());
                index++;
                if (!flag) {
                    if (index < count){
                        fsg.setNoRateMon((tempNRRM.doubleValue() - countzs
                                * 0.01 - 0.01)
                                + "");
                    }
                    else {
                        fsg.setNoRateMon((tempNRRM.doubleValue() - countzs * 0.01)
                                        + "");
                    }
                } else {
                    if (index < count) {
                        fsg.setNoRateMon((tempNRRM.doubleValue() + countzs
                                * 0.01 + 0.01)
                                + "");
                    }
                    else {
                        fsg.setNoRateMon((tempNRRM.doubleValue() + countzs * 0.01)
                                        + "");
                    }
                }
            }
            fsgl.setResultZS(list);
        }



        double wc2 = rateMonWc.doubleValue();

        if(wc2!=0) {
            int intwc = 0;
            boolean flag = false;
            if (wc < 0) {
                double f = Math.floor(wc * 100);
                if (f >= wc * 100) {
                    intwc = (int) f;
                } else {
                    intwc = (int) f + 1;
                }
            } else {
                flag = true;
                intwc = (int) Math.floor(wc * 100);
            }
            intwc = Math.abs(intwc);
            List<GoodsDetail> list = fsgl.getResultZS();
            int count = intwc % realCount;
            int countzs = intwc / realCount;
            int index = -1;
            for (int i = 0; i < list.size(); i++) {
                GoodsDetail fsg = list.get(i);
                if (fsg.isO()) {
                    continue;
                }
                BigDecimal tempNRRM =new BigDecimal(fsg.getRateMon());
                index++;
                if (!flag) {
                    if (index < count) {
                        fsg.setRateMon((tempNRRM.doubleValue() - countzs
                                * 0.01 - 0.01)
                                + "");
                    }
                    else {
                        fsg
                                .setRateMon((tempNRRM.doubleValue() - countzs * 0.01)
                                        + "");
                    }
                } else {
                    if (index < count) {
                        fsg.setRateMon((tempNRRM.doubleValue() + countzs
                                * 0.01 + 0.01)
                                + "");
                    }
                    else {
                        fsg.setRateMon((tempNRRM.doubleValue() + countzs * 0.01)
                                        + "");
                    }
                }
            }
            fsgl.setResultZS(list);
        }
        return fsgl;
    }
}
